﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using System.Data.OleDb;
using System.Data;

namespace DataAccess
{
    public partial class MSSQLDataAccess : IDataAccess
    {
        private OleDbCommand command;
        private OleDbConnection connection;
        private OleDbDataAdapter dataAdapter;

        public MSSQLDataAccess()
        {
            command = new OleDbCommand();

            connection = new OleDbConnection();

            dataAdapter = new OleDbDataAdapter(command);

            command.Connection = connection;

            command.CommandType = CommandType.Text;
        }


        #region IDataAccess Members

        public List<string> CheckConnection(ConnectionInformation connectionInformation)
        {
            string QueryString = "select name from sys.databases where Lower(name) not in ('master', 'model', 'msdb', 'tempdb')";

            return GenerateListFromColumn(ExecuteQuery(connectionInformation, QueryString), 0);
        }

        #endregion

        /// <summary>
        /// Execute Any Query String.
        /// Takes Connection Information Object and Query String.
        /// Throws Exception type exception.
        /// </summary>
        /// <param name="CI"></param>
        /// <param name="QueryString"></param>
        /// <returns></returns>
        private DataTable ExecuteQuery(ConnectionInformation CI, string QueryString)
        {
            DataTable dataTable = new DataTable();
           
            connection.ConnectionString = CI.getConnectionString();
            try
            {
                connection.Open();
                command.CommandText = QueryString;
                command.ExecuteNonQuery();

                dataAdapter.Fill(dataTable);
                
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Generate a List Object from a column of Table.
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="ColumnIndex"></param>
        /// <returns></returns>
        private List<string> GenerateListFromColumn(DataTable dataTable, int ColumnIndex)
        {
            List<string> List = new List<string>(); ;
            int count = dataTable.Rows.Count;

            for (int i = 0; i < count; i++)
                List.Add(dataTable.Rows[i][ColumnIndex].ToString());

            return List;
        }

        /// <summary>
        /// Generate Table List With Name Of schema. E.g. List<string> of mySchema.myTable.
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="SchemaIndex"></param>
        /// <param name="NameIndex"></param>
        /// <returns></returns>
        
        private List<string> GenerateTableList(DataTable dataTable, int SchemaIndex, int NameIndex)
        {
            List<string> List = new List<string>(); ;
            int count = dataTable.Rows.Count;

            for (int i = 0; i < count; i++)
                List.Add(dataTable.Rows[i][SchemaIndex].ToString() + "." + dataTable.Rows[i][NameIndex].ToString());

            return List;
        }

        private List<Column> GenerateColumnInformation(ConnectionInformation connectionInformation, string TableName, string QueryString)
        {
            string[] tableName = (TableName.Split("."[0]));

            StringBuilder sb = new StringBuilder("Select Column_name,data_type from ");
            sb.Append("INFORMATION_SCHEMA.COLUMNS where Table_Name='");
            sb.Append(tableName[1]);
            sb.Append("' AND Table_schema='");
            sb.Append(tableName[0]);
            sb.Append("'");
            QueryString = sb.ToString();


            DataTable dataTable = ExecuteQuery(connectionInformation, QueryString);
            List<Column> Columnlist = new List<Column>();
            int count = dataTable.Rows.Count;
            for (int i = 0; i < count; i++)
            {
                Columnlist.Add(new Column() { Name = dataTable.Rows[i][0].ToString(), Type1 = dataTable.Rows[i][1].ToString(), IsImported = true });
            }
            return Columnlist;
        }

        public void GetFullData(ConnectionInformation connectionInformation, ref TableInformation tableInformation)
        {
            try
            {
                if (tableInformation.DataTable.TableName == null)
                    throw new Exception("Please Call GetTableInformation() or GetViewInformation() Method first.");

                string QueryString = "Select * from " + tableInformation.DataTable.TableName;

                string tableName=tableInformation.DataTable.TableName;
                tableInformation.DataTable = ExecuteQuery(connectionInformation, QueryString);
                tableInformation.DataTable.TableName = tableName;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


    }

}
